package com.oyb.dao;

import com.oyb.entity.Classes;
import com.oyb.entity.Page;
import com.oyb.entity.Student;
import com.oyb.util.ConnectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StuDao {
    //查询所有学生
    public List<Student> queryAllStudent(Page page) {
        List<Student> students = new ArrayList<>();
        Connection connection = ConnectionUtils.getconnection();
        String sql = "select s.id,s.name,s.sex,s.cid,c.class_name,c.class_count from" +
                " student s join classes c on s.cid=c.id limit ?,?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setObject(1,(page.getPage()-1)*page.getPageSize());
            statement.setObject(2,page.getPageSize());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Student student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setSex(resultSet.getInt("sex"));
                Classes classes = new Classes();
                classes.setId(resultSet.getInt("cid"));
                classes.setClass_name(resultSet.getString("class_name"));
                classes.setClass_count(resultSet.getInt("class_count"));
                student.setClasses(classes);
                students.add(student);
            }
            return students;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionUtils.close(statement,connection,null);
        }
        return null;
    }
    //插入学生(新增)
    public int insertStudent(Student student){

        Connection connection = ConnectionUtils.getconnection();
        //connection.setAutoCommit(false);1.开启事务
        String sql = "insert into student value (?,?,?,?)";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setObject(1,student.getId());
            statement.setObject(2,student.getName());
            statement.setObject(3,student.getSex());
            statement.setObject(4,student.getCid());
            return  statement.executeUpdate();
            //connection.commit();2.手动提交事务
        }catch (SQLException throwables) {
            //connection.rollback();3.回滚事务
            throwables.printStackTrace();
        }finally {
            ConnectionUtils.close(statement,connection,null);
        }
        return 0;
    }

//查询所有学生的数量也就是记录的条数
    public int querystudentcount() {
        String sql ="select count(*) as studentcount from student";
        Connection conn = ConnectionUtils.getconnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = conn.prepareStatement(sql);
            //执行查询
            resultSet = ps.executeQuery();
            //获得总条数
            if (resultSet.next()) {
                //获取聚合函数所在的列只能用下标或者列的别名
                //return resultSet.getInt("1");
                return resultSet.getInt("studentcount");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close( ps, conn,resultSet);
        }
        return 0;
    }
}
